package vn.com.demand.lux.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import vn.com.demand.lux.criteria.UpdateIssuingExpenseCriteria;
import vn.com.demand.lux.model.UpdateIssuingExpenseBean;
import vn.com.demand.util.DpUtils;

/**
 * 
 * @author Huynh Tan Tai <httcntt@yahoo.com>
 *
 */
public class UpdateIssuingExpenseDao {
	private static SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
	
	public static List<UpdateIssuingExpenseBean> searchUpdateIssuingExpenses(UpdateIssuingExpenseCriteria criteria){
		if (null != criteria.getFromDate() && null != criteria.getToDate()) {
			Connection connection = null; 
			try {
			connection = DpUtils.getConnection();
			StringBuffer sql = new StringBuffer("SELECT g.project AS ContractNumber, g.reknr AS LedgerCode, k.oms25_0 AS LedgerName, ");
			sql.append("g.dagbknr AS JournalCode, g.bkstnr AS EntryNumber, g.faktuurnr AS OurReference, g.bdr_hfl AS Amount ");
			sql.append("FROM dbo.gbkmut g INNER JOIN dbo.grtbk k ON g.reknr = k.reknr ");
			sql.append("WHERE (NOT (g.transtype IN ('V', 'B'))) AND (LEFT(LTRIM(g.reknr), 1) BETWEEN 6 AND 8) ");
			sql.append("AND (LTRIM(RTRIM(g.reknr)) <> '641300') AND (g.datum BETWEEN ");
			sql.append("CONVERT(DATETIME, '2007-12-01', 102) AND CONVERT(DATETIME, '2007-12-31', 102)) ");
			sql.append("AND (LTRIM(RTRIM(g.Selcode)) IN :list) AND (LTRIM(RTRIM(g.reknr)) = '632100')");
			String str_sql = sql.toString();
			str_sql = str_sql.replaceAll("632100", criteria.getOriginalLedgerCode());
			str_sql = str_sql.replaceAll("641300", criteria.getChangedLedgerCode());
			str_sql = str_sql.replaceAll(":list", DpUtils.toArrayStringInDB(criteria.getIssuingPurposes()));
			str_sql = str_sql.replaceAll("2007-12-01", df.format(criteria.getFromDate()));
			str_sql = str_sql.replaceAll("2007-12-31", df.format(criteria.getToDate()));
			ResultSetHandler handler = new BeanListHandler(UpdateIssuingExpenseBean.class);
			QueryRunner runner = new QueryRunner();
			return (List<UpdateIssuingExpenseBean>) runner.query(connection, str_sql, handler);
			} catch (SQLException e) {
				e.printStackTrace();
				DpUtils.rollback(connection);
			} finally {
				DpUtils.releaseConnection(connection);
			}
		}
		return null;
	}
	
	public static void updateIssuingExpense(UpdateIssuingExpenseCriteria criteria, List<UpdateIssuingExpenseBean> list) throws SQLException{
		Connection connection = null;
		try {
			connection = DpUtils.getConnection();
			connection.setAutoCommit(false);
			connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
			for (UpdateIssuingExpenseBean bean : list) {
				String sql1 = "update gbkmut set reknr = 'ChangedLedgerCode' where ltrim(rtrim(dagbknr)) = 'JournalCode' and ltrim(rtrim(bkstnr)) = 'EntryNumber' and ltrim(rtrim(faktuurnr)) = 'OurReference' and ltrim(rtrim(reknr)) = 'OriginalLedgerCode'";
				String changedLedgerCode = criteria.getChangedLedgerCode();
				String desChangedLedgerCode = "";
				for (int i = 0; i < 9 - changedLedgerCode.length(); i++) {
					desChangedLedgerCode += " ";
				}
				criteria.setChangedLedgerCode(desChangedLedgerCode + changedLedgerCode);
				sql1 = sql1.replaceAll("ChangedLedgerCode", criteria.getChangedLedgerCode());
				sql1 = sql1.replaceAll("JournalCode", bean.getJournalCode().trim());
				sql1 = sql1.replaceAll("EntryNumber", bean.getEntryNumber().trim());
				sql1 = sql1.replaceAll("OurReference", bean.getOurReference().trim());
				sql1 = sql1.replaceAll("OriginalLedgerCode", criteria.getOriginalLedgerCode().trim());
				
				String sql2 = "update amutas set reknr = 'ChangedLedgerCode' where ltrim(rtrim(dagbknr)) = 'JournalCode' and ltrim(rtrim(bkstnr)) = 'EntryNumber' and ltrim(rtrim(faktuurnr)) = 'OurReference' and ltrim(rtrim(reknr)) = 'OriginalLedgerCode'";
				sql2 = sql2.replaceAll("ChangedLedgerCode", criteria.getChangedLedgerCode());
				sql2 = sql2.replaceAll("JournalCode", bean.getJournalCode().trim());
				sql2 = sql2.replaceAll("EntryNumber", bean.getEntryNumber().trim());
				sql2 = sql2.replaceAll("OurReference", bean.getOurReference().trim());
				sql2 = sql2.replaceAll("OriginalLedgerCode", criteria.getOriginalLedgerCode().trim());
				QueryRunner runner = new QueryRunner();
				runner.update(connection, sql1);
				runner.update(connection, sql2);
			}
			connection.commit();
		} catch (SQLException e) {
			DpUtils.rollback(connection);
			//connection.rollback();
			e.printStackTrace();
			throw new SQLException("Can not update");
		}
		finally {
			DpUtils.releaseConnection(connection);
		}
	}
}
